import ctypes
ctypes.windll.user32.ShowWindow(ctypes.windll.kernel32.GetConsoleWindow(), 0)
#上面两行确保批处理运行时不显示CMD窗口

from asyncio import exceptions
from tkinter import *
from tkinter import ttk
import tkinter.messagebox
import tkinter as tk
from tkinter import Label
from turtle import color
import os
import sys
import pymysql
import pymssql
import openpyxl as vb
import datetime
import time
import copy
import getpass
from zeep import Client
import re
from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart

def windows(): 
    todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
    #Create an instance
    win = Tk()
    win.title('TCC Reliability: Test Information Query Tool V'+str(version)+' '+release_date)
    # win.geometry('740x430')
    win.geometry('870x500')  #宽X高
    #.建立mysql的连接
    conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
            password='123456',port=3306,charset='utf8')
    # 获得游标
    cursor = conn.cursor()  
    
    lb0 = Label(win, text = "GTS信息查询/模板创建工具 V"+str(version), font = "tahoma 22 normal",fg='blue')
    lb0.place(x=200,y=20)
    lb1 = Label(win, text = "Product", font = "tahoma 12 normal")
    lb1.place(x=35,y=80)
    
    def show_productlist(*args):
        print("Event: ComboboxSelected")
        print(cbx_1.get())

    productlist=['']        
    testlist=['ALT']
    cursor.execute('''select product from alt_infor group by product order by product ASC''')
    # cursor.execute('''select product from ota_infor where product like concat('%',{},'%') group by product '''.format(input))
    products = cursor.fetchall()
    for i in products:
        productlist.append(i[0])
    cursor.execute('select test_name from ota_infor group by test_name')
    tests = cursor.fetchall()
    for j in tests:
        testlist.append(j[0])


    
    def show_select():        
        print(value.get())
        input=value.get()
        print('productlist0:',productlist)
        
        # 点击选择框右侧箭头时，触发选择动作，如果里面不是已存在的产品也不为空，则清空productlist, 重新按输入搜索；否则，else则显示全部产品:  
        if input not in productlist and input !='':                      
            productlist.clear()
            print('productlist1:',productlist)            
            cursor.execute('''select product from alt_infor where product like '{}%' group by product'''.format(input))
            products = cursor.fetchall()
            for i in products:
                productlist.append(i[0])
            print('productlist2:',productlist)
            
        else:
            productlist.clear()
            cursor.execute('''select product from alt_infor group by product order by product ASC''')
            products = cursor.fetchall()
            for i in products:
                productlist.append(i[0])
        cbx_1["values"] = productlist

     
    value = StringVar()
    cbx_1 = ttk.Combobox(win, width = 20, height = 8,textvariable = value, postcommand = show_select)
    cbx_1.place(x=35,y=105)
    
    cbx_1.configure(state = "normal")
    
    #add data to combobox
    cbx_1["values"] = productlist
    #set the initial data [index =2] to shows up when win generated
    cbx_1.current(0)
    
    #bind a event
    cbx_1.bind("<<ComboboxSelected>>", show_productlist)
    
    #======================================================================================================
    #create a Label
    lb2 = Label(win, text = "Test", font = "tahoma 12 normal")
    lb2.place(x=220,y=80)
    
    def show_testlist(*args):
        print("Event: ComboboxSelected")
        print(cbx_2.get())
    
    
    #Create a combobox, and tighter it to value
    cbx_2 = ttk.Combobox(win, width = 20, height = 8)
    cbx_2.place(x=220,y=105)
    
    #set cbx_2 as readonly
    cbx_2.configure(state = "readonly")
    
    #add data to combobox
    cbx_2["values"] = testlist
    #set the initial data [index =2] to shows up when win generated
    cbx_2.current(1)  
    #bind a event
    cbx_2.bind("<<ComboboxSelected>>", show_testlist)

    #V3.4开始为了防病毒查杀，把crawler 引入到本体中
    def crawler(test_no): 
        
        test_no_digit = re.findall('\d{6}',test_no)
        if test_no_digit!=[]:
            test_no='WH'+test_no_digit[0]
    
            

            #.建立mysql的连接
            altserver='100.64.6.151'
            conn=pymssql.connect(host=altserver,user = "global_alt_guest",password = "global@alt@guest",database = "ALTDBV3")
            # 获得游标
            cursor = conn.cursor()  
            cursor.execute('''SELECT SA.Unit_No,SU.SN,SU.Product,SU.Vintage,SA.Internal_ID,R.ALTEng,T.Name from SamplesUploaded AS SU 
                    LEFT OUTER JOIN SamplesAssignment AS SA ON  SA.Internal_ID=SU.ID 
                    LEFT OUTER JOIN Registration AS R ON SA.Test_Seqno=R.Test_Seqno 
                    LEFT OUTER JOIN Tests AS T ON  R.Test_ID=T.ID 
                    WHERE (SA.Test_Seqno=%s) ORDER BY SA.Unit_No''', test_no)


            sn_list=[]
            test_infor=cursor.fetchall()
            # print(test_infor)

            sample_no=len(test_infor)
            for i in test_infor:
                sn_list.append(i[1])   
            
                test_name=i[6]


                vintage_name=i[3]
            
                technician_name=i[5]
                com_date=''

        else:        
            sn_list=[]
            test_name='0'
            sample_no='0'
            test_no='0'
            vintage_name='0'
            com_date=''
            technician_name='0'
        
        print(sn_list)
        print(test_name)
        print(sample_no)
        print(test_no)
        print(vintage_name)
        print(technician_name)

        return(sn_list,test_name,sample_no,test_no,vintage_name,com_date,technician_name)
        

    def query():      
        #.获取当前日期        
        pre_days_upload=(datetime.datetime.now()+datetime.timedelta(days=-60)).strftime('%Y-%m-%d')  
        m1daysago = datetime.datetime.strptime(pre_days_upload,"%Y-%m-%d")       
        pre_days_upload3=(datetime.datetime.now()+datetime.timedelta(days=-3)).strftime('%Y-%m-%d')  
        m3daysago = datetime.datetime.strptime(pre_days_upload,"%Y-%m-%d")      
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')  
         
 
        

        product_seletion = cbx_1.get()
        test_selection = cbx_2.get()
        info=[]
        rowinfo=[]

        # 清空表格
        x=table.get_children()
        for item in x:
            table.delete(item)
        y=table0.get_children()
        for item in y:
            table0.delete(item)

        #对于ALT的查询，查alt_infor
        if test_selection =='ALT':
            cursor.execute('select * from alt_infor where product =%s ', product_seletion)
            contents=cursor.fetchall()
            n=len(contents)
            if n==0:
                tk.messagebox.showinfo(title='Hi', message='没有找到相关数据信息！')
                report_status='NA'
            else:
                for i in contents:
                    for j in range(1,18):
                        rowinfo.append(i[j])
                    print(rowinfo)
                    rowinfoi=copy.deepcopy(rowinfo)
                    info.append(rowinfoi)
                    rowinfo.clear()
                    print(rowinfoi)
                
                report_status='NA'
            # 插入数据到table(列表中包含行组成的小列表)
                for index, data in enumerate(info):
                    table0.insert('', END, values=data,tags='tag')  # 添加数据到末尾, tags为改变表格内容背景及文字的方法
                info.clear()  
            
            #Query information record 记录查询动作到数据库                
            query='''insert into test_query_record (user,query_product,query_test,query_no,query_time,Date) values(%s,%s,%s,%s,%s,%s)'''
            user = getpass.getuser()  
            query_product = product_seletion
            query_test = test_selection
            query_no = 1
            query_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')            
            Date=todaycolumn
            values =(user,query_product,query_test,query_no,query_time,Date)
            # 执行sql语句
            cursor.execute(query,values) 
            conn.commit() 


          

        #对于Edu06的查询，查ota_infor    
        if test_selection =='EDu06':
            cursor.execute('select * from ota_infor where product =%s and test_name=%s',(product_seletion,test_selection))
            contents=cursor.fetchall()
            n=len(contents)
            if n==0:
                tk.messagebox.showinfo(title='Hi', message='没有找到相关数据信息！')
            else:
                for i in contents:
                    for j in range(1,12):
                        rowinfo.append(i[j])
                    print(rowinfo)
                    rowinfoi=copy.deepcopy(rowinfo)
                    info.append(rowinfoi)
                    rowinfo.clear()
                    print(rowinfoi)
                
                # print(info)
            # 插入数据到table(列表中包含行组成的小列表)
                for index, data in enumerate(info):
                    table.insert('', END, values=data,tags='tag')  # 添加数据到末尾
                info.clear()

                # 输出到Excel

                path = os.path.join(os.path.expanduser("~"), 'Desktop')  #获取桌面的路径
                path0=path+'\GTS测试信息'
                if not os.path.exists(path0):  #判断桌面文件夹是否存在，不存在创建
                    os.mkdir(path0)
                else: #删除文件夹中2天前文件，只留今天和昨天的
                    for file in os.listdir(path0):    
                        filewithpath = path0+'\\'+file   
                        print(file)   
                        filetime = os.path.getctime(filewithpath)       
                        timearr = time.localtime(filetime)     #输出：time.struct_time(tm_year=2020, tm_mon=11, tm_mday=15, tm_hour=12, tm_min=49, tm_sec=10, tm_wday=6, tm_yday=320, tm_isdst=0)
                        standardtime = time.strftime('%Y-%m-%d %H:%M:%S',timearr)     

                        if standardtime<str(m1daysago):  
                            os.remove(filewithpath)
                

                workbook = vb.load_workbook(r'\\10.114.184.9\Tcc_Rel\07 Project management\EDu03&EDu06 OTA测试信息\Test Report Template\EDu06 Radiation Test Report Rev A.xlsx')
                filepath = path0+'/'+product_seletion+'_'+test_selection+'_Test Report.xlsx'
                
                workbook.save(filepath)
                workbook = vb.load_workbook(filepath)          
                
                worksheet = workbook['TRP&TIS']

                cursor.execute('''select Band,Channel from ota_infor where product =%s and test_name=%s and TRP_EIS='TRP' ''',(product_seletion,test_selection))
                trp_contents=cursor.fetchall()
                trp_n=len(trp_contents)
                cursor.execute('''select Antenna,Band,Channel,Angle from ota_infor where product =%s and test_name=%s and TRP_EIS='EIS' ''',(product_seletion,test_selection))
                eis_contents=cursor.fetchall()
                eis_n=len(eis_contents)
        
                
                #格式设置

                fontstyle1 = vb.styles.Font(name='Arial',size=10)    #正文
                alignstyle = vb.styles.Alignment(horizontal='center',vertical='center')  #居中
                alignstyle1 = vb.styles.Alignment(horizontal='left',vertical='center')  #居中
            

                #输出查到的数据到Excel  
                m=21-trp_n
                for row in range(m,21):           
                    worksheet.cell(row,4,value = trp_contents[row-m][0]).font = fontstyle1
                    worksheet.cell(row,5,value = trp_contents[row-m][1]).font = fontstyle1
                k=49-eis_n
                for row in range(k,49):           
                    worksheet.cell(row,2,value = eis_contents[row-k][0]).font = fontstyle1
                    worksheet.cell(row,3,value = eis_contents[row-k][1]).font = fontstyle1
                    worksheet.cell(row,4,value = eis_contents[row-k][2]).font = fontstyle1
                    worksheet.cell(row,5,value = eis_contents[row-k][3]).font = fontstyle1
                
                #隐藏不用的空白行
                for i in range(6,m):
                    worksheet.row_dimensions[i].hidden=True
                for i in range(24,k):
                    worksheet.row_dimensions[i].hidden=True


                
                # if test_selection =='EDu03':
                #     workbook.remove(workbook['Summary'])
                #     # workbook.remove(workbook['WLAN'])
                #     workbook.remove(workbook['NFC&Desense'])

                target_dir_desense = r'\\10.114.184.9\Tcc_Rel\07 Project management\EDu03&EDu06 OTA测试信息\Desense'
                product_dir = target_dir_desense+'\\'+ product_seletion +'.jpg'

                #判断Desense图片是否存在，存在则创建，不存在则略过。
                if os.path.exists(product_dir):
                    worksheet = workbook['NFC&Desense']
                    mypic = vb.drawing.image.Image(product_dir)
                    mypic.height =130
                    mypic.width =1000
                    worksheet.add_image(mypic,'B16')
              



                def test_query():
                    test_no=entry_days.get()  
                    if test_no=='':
                        save()
                        report_status='Simple'
                    else:   
                        # query_result=gts_crawler.crawler(test_no)  #返回元祖，20241013更新成下面这句
                        query_result=crawler(test_no)  #返回元祖
                        sn_list=query_result[0]
                        test_name=query_result[1]
                        sample_no=query_result[2]
                        test_no=query_result[3]

                        vintage_name=query_result[4]
                        com_date=query_result[5]
                        technician_name=query_result[6]


                        if sample_no<=6 and 'EDu06' in test_name and test_no !='0':
                            print('Edu06实验号查询有效！')
                            worksheet = workbook['Summary']
                            # m=len(sn_list)
                            # for i in range(m,7):
                            #     sn_list.append('')
                            # print(sn_list)
                            for row in range(11,17):                          
                                worksheet.cell(row,3,value = sn_list[row-11]).font = fontstyle1 
                                worksheet.cell(row,3).alignment = alignstyle 
                                 
                            
                            worksheet.cell(3,3,value=product_seletion).font=fontstyle1
                            worksheet.cell(3,3).alignment = alignstyle1

                            worksheet.cell(4,3,value=technician_name).font=fontstyle1
                            worksheet.cell(4,3).alignment = alignstyle1

                            worksheet.cell(5,3,value=com_date).font=fontstyle1
                            worksheet.cell(5,3).alignment = alignstyle1                      
                                                                                    
                            worksheet.cell(6,3,value=test_no).font=fontstyle1
                            worksheet.cell(6,3).alignment = alignstyle1

                            save()
                            filename_modify(test_no,vintage_name,test_name)
                            report_status='Full'
                        else:
                            tk.messagebox.showwarning(title='Hi', message='实验号查询无效！')
                            save()
                            report_status='Simple'

                    query_record(report_status,test_no) 


                def save():
                    workbook.save(filepath)
                    tk.messagebox.showinfo(title='Hi', message='Excel报告模板已输出到桌面“GTS测试信息”文件夹中！')
                    report_status='Simple'
                    window_test_no.destroy()
                #文件改名字，普通模板到详细报告名
                def filename_modify(test_no,vintage_name,test_name):
                    target_dir=path0
                    oldfilename = product_seletion+'_'+test_selection+'_Test Report.xlsx'                       
                    fileExpand = os.path.splitext(oldfilename)[1]  
                    #改名字
                    filenewname = test_no+' '+product_seletion+' '+vintage_name+' '+test_name+' '+'report'+fileExpand
                    oldpath = os.path.join(target_dir,oldfilename)
                    newpath = os.path.join(target_dir,filenewname)
                    #如果已经存在同名文件，先删除，然后才能改成相同的文件名
                    if os.path.exists(newpath):
                        os.remove(newpath)
                    os.rename(oldpath,newpath)  
                                  



                window_test_no=tk.Toplevel(win)
                window_test_no.geometry('350x150')
                window_test_no.title('实验号输入')

                days_enter=tk.StringVar()
                tk.Label(window_test_no,text='请输入实验号:').place(x=10,y=20)
                tk.Label(window_test_no,text='  ** 正确输入实验号可以输出更加详细的报告模板, 否则只能'+'\n'+'输出普通模板！(大约需要10秒钟）',fg='blue',font=('Arial',9)).place(x=10,y=110)
                entry_days=tk.Entry(window_test_no,textvariable=days_enter,show=None)
                entry_days.place(x=130,y=20)  

                btn_confirm_days=tk.Button(window_test_no,text='确定',width=8,height=1, command=test_query)
                btn_cancel_days=tk.Button(window_test_no,text='取消',width=8,height=1, command=save)
                btn_confirm_days.place(x=120,y=50)
                btn_cancel_days.place(x=220,y=50)

            







        #删除桌面上的旧版本文件，需要时间在3天前，防止老的版本把新版本删除
        path = os.path.join(os.path.expanduser("~"), 'Desktop')  #获取桌面的路径
        for file in os.listdir(path):
            filename = os.path.splitext(file)[0]  
            fileExpand = os.path.splitext(file)[1]
        
            if 'GTS_Infor_Query' in filename:                     
                pathfile=path+'\\'+file                        
                filetime0 = os.path.getctime(pathfile)       
                timearr0 = time.localtime(filetime0)     #输出：time.struct_time(tm_year=2020, tm_mon=11, tm_mday=15, tm_hour=12, tm_min=49, tm_sec=10, tm_wday=6, tm_yday=320, tm_isdst=0)
                standardtime0 = time.strftime('%Y-%m-%d %H:%M:%S',timearr0)     
                print(standardtime0)

                if standardtime0<str(m3daysago):  #自动删除创建时间在2个月之前的文件
                    try:
                        os.remove(pathfile)
                    except exceptions:
                        continue

        #Query information record
        # query='''insert into test_query_record (user,query_product,query_test,query_no,query_time) values(%s,%s,%s,%s,%s)'''
        # user = getpass.getuser()  
        # query_product = product_seletion
        # query_test = test_selection
        # query_no = 1
        # query_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') 

        # values =(user,query_product,query_test,query_no,query_time)
        # # 执行sql语句
        # cursor.execute(query,values) 
        # conn.commit() 
        

        #对于Edu03 的查询，查ota_infor数据库表格   
        if test_selection.startswith('EDu03'): #判断字符串是否以Edu03开头
            #查询产品对应的类型category
            cursor.execute('select category from alt_infor where product =%s', product_seletion)
            content=cursor.fetchone()
            product_category=content[0]
            
            if product_category=='ODM' or product_category=='In-house':
                product_category='Normal'

            #判断选择的实验类型(ODM,INhouse,FT)是否和Alt_infor表格中的一致，当然表格中只有normal/FT两种类型
            #product_category只有FT和Normal两种。
            if product_category in test_selection: #看产品类型和所选试验是否匹配，如果匹配则取出全部数据          
                cursor.execute('select * from ota_infor where product =%s and test_name=%s',(product_seletion,test_selection))
                contents=cursor.fetchall()
                n=len(contents)
                print('checkpoint',n)
                if n==0:
                    tk.messagebox.showinfo(title='Hi', message='没有找到相关数据信息！')
                else:
                    for i in contents:
                        for j in range(1,13):
                            rowinfo.append(i[j])
                        print(rowinfo)
                        rowinfoi=copy.deepcopy(rowinfo)
                        info.append(rowinfoi)
                        rowinfo.clear()
                        print(rowinfoi)
                    
                    # print(info)
                # 插入数据到table(列表中包含行组成的小列表)
                    for index, data in enumerate(info):
                        table.insert('', END, values=data,tags='tag')  # 添加数据到末尾
                    info.clear()

                    # 输出到Excel
                    path = os.path.join(os.path.expanduser("~"), 'Desktop')  #获取桌面的路径
                    path0=path+'\GTS测试信息'
                    if not os.path.exists(path0):  #判断桌面文件夹是否存在，不存在创建
                        os.mkdir(path0)
                    else: #删除文件夹中2天前文件，只留今天和昨天的
                        for file in os.listdir(path0):    
                            filewithpath = path0+'\\'+file   
                            print(file)   
                            filetime = os.path.getctime(filewithpath)       
                            timearr = time.localtime(filetime)     #输出：time.struct_time(tm_year=2020, tm_mon=11, tm_mday=15, tm_hour=12, tm_min=49, tm_sec=10, tm_wday=6, tm_yday=320, tm_isdst=0)
                            standardtime = time.strftime('%Y-%m-%d %H:%M:%S',timearr)     

                            if standardtime<str(m1daysago):  
                                os.remove(filewithpath)
                    
                    target_dir0 = r'\\10.114.184.9\Tcc_Rel\07 Project management\EDu03&EDu06 OTA测试信息\Test Report Template'
                    Edu03_template_dir = target_dir0+'\\'+test_selection+'.xlsx'
                    workbook = vb.load_workbook(Edu03_template_dir)
                    filepath = path0+'/'+product_seletion+'_'+test_selection+'_Test Report.xlsx'
                    alignstyle = vb.styles.Alignment(horizontal='center',vertical='center')  #居中
                    alignstyle1 = vb.styles.Alignment(horizontal='left',vertical='center')  #居中
                    
                    workbook.save(filepath)
                    workbook = vb.load_workbook(filepath)          
                    
                    worksheet = workbook['TRP&TIS']

                    cursor.execute('''select Band,Channel from ota_infor where product =%s and test_name=%s and TRP_EIS='TRP' ''',(product_seletion,test_selection))
                    trp_contents=cursor.fetchall()
                    trp_n=len(trp_contents)
                    cursor.execute('''select Antenna,Band,Channel,Angle from ota_infor where product =%s and test_name=%s and TRP_EIS='EIS' ''',(product_seletion,test_selection))
                    eis_contents=cursor.fetchall()
                    eis_n=len(eis_contents)
            
                    
                    #格式设置

                    fontstyle1 = vb.styles.Font(name='Arial',size=10)    #正文
                        

                    #输出查到的数据到Excel  
                    m=21-trp_n
                    for row in range(m,21):           
                        worksheet.cell(row,4,value = trp_contents[row-m][0]).font = fontstyle1
                        worksheet.cell(row,5,value = trp_contents[row-m][1]).font = fontstyle1
                    k=49-eis_n
                    for row in range(k,49):           
                        worksheet.cell(row,2,value = eis_contents[row-k][0]).font = fontstyle1
                        worksheet.cell(row,3,value = eis_contents[row-k][1]).font = fontstyle1
                        worksheet.cell(row,4,value = eis_contents[row-k][2]).font = fontstyle1
                        worksheet.cell(row,5,value = eis_contents[row-k][3]).font = fontstyle1
                    
                    #隐藏不用的空白行
                    for i in range(6,m):
                        worksheet.row_dimensions[i].hidden=True
                    for i in range(24,k):
                        worksheet.row_dimensions[i].hidden=True




                    #处理EDu03输入实验号的SN查询
                    def test_query():
                        test_no=entry_days.get()  
                        if test_no=='':
                            save()
                            report_status='Simple'
                        else:   
                            # query_result=gts_crawler.crawler(test_no)  #返回元祖 20241013更新成下面这句
                            query_result=crawler(test_no) 
                            #返回return(sn_list,test_name,sample_no,test_no,vintage_name,com_date,technician_name)
                            sn_list=query_result[0]
                            test_name=query_result[1]
                            sample_no=query_result[2]
                            test_no=query_result[3]
                            vintage_name=query_result[4]
                            com_date=query_result[5]
                            technician_name=query_result[6]

                            template_no= test_selection[6]  #从模板名称中提取样机个数
                            
                            
                            if sample_no<=int(template_no) and 'EDu03' in test_name and test_no !='0': 
                                                                                    
                                worksheet = workbook['Summary']
                                print('checkpoint ****** 判断成立')
                                for row in range(10,10+int(sample_no)):           
                                    worksheet.cell(row,2,value = sn_list[row-10]).font = fontstyle1 
                                    worksheet.cell(row,2).alignment = alignstyle 


                                worksheet.cell(3,2,value=product_seletion).font=fontstyle1
                                worksheet.cell(3,2).alignment = alignstyle1

                                worksheet.cell(4,2,value=technician_name).font=fontstyle1
                                worksheet.cell(4,2).alignment = alignstyle1

                                worksheet.cell(5,2,value=com_date).font=fontstyle1
                                worksheet.cell(5,2).alignment = alignstyle1

                                worksheet.cell(6,2,value=test_no).font=fontstyle1
                                worksheet.cell(6,2).alignment = alignstyle1
                                
                                save()
                                filename_modify(test_no,vintage_name,test_name)
                                report_status='Full'
                                
                                
                            else:
                                tk.messagebox.showwarning(title='Hi', message='实验号查询无效！')
                                save()
                                report_status='Simple'

                            
                        query_record(report_status,test_no)
                        


                    def save():
                     # #添加Desense图
                        target_dir_desense = r'\\10.114.184.9\Tcc_Rel\07 Project management\EDu03&EDu06 OTA测试信息\ESD Illustration'
                        product_dir = target_dir_desense+'\\'+ product_seletion +'.jpg'
                        #判断静电图是否存在，存在则添加，不存在则删除之前存的中间文件workbook,并报错！
                        if os.path.exists(product_dir):

                            worksheet = workbook['Test zones']
                            mypic = vb.drawing.image.Image(product_dir)
                            mypic.height =600
                            mypic.width =1200
                            worksheet.add_image(mypic,'A2')
                        

                            workbook.save(filepath)

                            tk.messagebox.showinfo(title='Hi', message='Excel报告模板已输出到桌面“GTS测试信息”文件夹中！')
                        else:
                            os.remove(filepath)
                            tk.messagebox.showerror(title='Hi', message='静电图文件不存在，模板创建失败！')
                        window_test_no.destroy()

                    #文件改名字，普通模板到详细报告名
                    def filename_modify(test_no,vintage_name,test_name):
                        target_dir=path0
                        oldfilename = product_seletion+'_'+test_selection+'_Test Report.xlsx'                       
                        fileExpand = os.path.splitext(oldfilename)[1]  
                        #改名字
                        filenewname = test_no+' '+product_seletion+' '+vintage_name+' '+test_name+' '+'report'+fileExpand
                        oldpath = os.path.join(target_dir,oldfilename)
                        newpath = os.path.join(target_dir,filenewname)
                        #如果已经存在同名文件，先删除，然后才能改成相同的文件名
                        if os.path.exists(newpath):
                            os.remove(newpath)
                        os.rename(oldpath,newpath)


                     
                        

                    

                    window_test_no=tk.Toplevel(win)
                    window_test_no.geometry('350x150')
                    window_test_no.title('实验号输入')

                    days_enter=tk.StringVar()
                    tk.Label(window_test_no,text='请输入实验号:').place(x=10,y=20)
                    tk.Label(window_test_no,text='  ** 正确输入实验号可以输出更详细的模板, 否则只能'+'\n'+'输出普通模板！(大约需要10秒钟进行查询）',fg='blue',font=('Times',9)).place(x=10,y=110)
                    entry_days=tk.Entry(window_test_no,textvariable=days_enter,show=None)
                    entry_days.place(x=130,y=20)  

                    btn_confirm_days=tk.Button(window_test_no,text='确定',width=8,height=1, command=test_query)
                    btn_cancel_days=tk.Button(window_test_no,text='取消',width=8,height=1, command=save)
                    btn_confirm_days.place(x=120,y=50)
                    btn_cancel_days.place(x=220,y=50)



            else:
                tk.messagebox.showerror(title='Hi', message='模板类型与该产品不匹配！')
            

       



        #删除桌面上的旧版本文件，需要时间在3天前，防止老的版本把新版本删除
        path = os.path.join(os.path.expanduser("~"), 'Desktop')  #获取桌面的路径
        for file in os.listdir(path):
            filename = os.path.splitext(file)[0]  
            fileExpand = os.path.splitext(file)[1]
        
            if 'GTS_Infor_Query' in filename:                     
                pathfile=path+'\\'+file                        
                filetime0 = os.path.getctime(pathfile)       
                timearr0 = time.localtime(filetime0)     #输出：time.struct_time(tm_year=2020, tm_mon=11, tm_mday=15, tm_hour=12, tm_min=49, tm_sec=10, tm_wday=6, tm_yday=320, tm_isdst=0)
                standardtime0 = time.strftime('%Y-%m-%d %H:%M:%S',timearr0)     
                print(standardtime0)

                if standardtime0<str(m3daysago):  
                    os.remove(pathfile)
      
        #Query information record 记录查询动作到数据库
        def query_record(report_status,test_no):            
            query='''insert into test_query_record (user,query_product,query_test,query_no,query_time,report,Date) values(%s,%s,%s,%s,%s,%s,%s)'''
            user = getpass.getuser()  
            query_product = product_seletion
            query_test = test_selection
            query_no = test_no
            query_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') 
            report=report_status
            Date=todaycolumn

            values =(user,query_product,query_test,query_no,query_time,report,Date)
            # 执行sql语句
            cursor.execute(query,values) 
            conn.commit() 
       
               
    def email_summary():
        current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')   
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
        conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor() 
        mailinfor=[]

        cursor.execute('''SELECT * FROM gts_update_record where left(update_date,10)=%s''',todaycolumn)
        updateinfor=cursor.fetchall()  
        for i in updateinfor:
            mailinfor.append(i[1]+'  by: '+i[2]+' at '+ str(i[3]))



        cursor.close()
        conn.close()
        
          
     
        #----------------邮件发送--------------------------------------

        from_addr='tcc_reliability@motorola.com'
        to_addr=['lijun24@lenovo.com','nizhao2@lenovo.com','wangjin24@lenovo.com']   


        # 构建message
        msg = MIMEMultipart()

        msg['From'] = Header(from_addr)
        msg['To'] = Header(",".join(to_addr))  
        # msg['Subject'] = Header('Award_Audit Record ') #utf-8可以不加
        msg['Subject'] = Header('GTS工具数据库更新：'+current_time+''+'  By: '+user_name,'utf-8') #utf-8可以不加


        # 以html格式构建邮件内容
        send_str = '<html><body>'

        # 下面这个方法可以变换字体及颜色
        # send_str += '<left>'+ '<font size="3" color="red">'+str_name_t+'</font>' +'</left>'  
        send_str += '<center> <br></center>'  #有这行导致两图之间间距过大
        for text in mailinfor:
            send_str += '<left> 当日记录：'+text+'<br></left>'


        send_str += '<center> </center>'  #有这行导致两图之间间距过大
        send_str += '<center> <br></center>'  #有这行导致两图之间间距过大

        send_str += '<center> *** 本邮件在数据更新时发出，显示当日所有更新记录。*** </center>'
        send_str += '</body></html>'

        # 添加邮件内容
        content = MIMEText(send_str, _subtype='html', _charset='utf8')
        msg.attach(content)

        # API 地址
        api_url = 'http://100.64.5.171/WebServices/WS_EMAIL/WS_EMAIL.asmx?wsdl'
        email_params = {
            'message':  msg.as_string()  # 将邮件内容转换为字符串
        }

        # 发送邮件
        try:
            client = Client(api_url)
            response = client.service.sendEmail(**email_params)
            msg=("Email sent successfully:", response)
        except Exception as e:
            msg=("Failed to send email:", e)

        print(msg)  
           



    def update(): 
        #1.获取当前日期
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
        tomorrow=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%m%d')
        tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d') 
        tomorrowcolumn1=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d') 
        pre_days=(datetime.datetime.now()+datetime.timedelta(days=-1)).strftime('%Y-%m-%d')
        update_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')   
        #pre_days_upload 定义上传之前多久到达的实验
        pre_days_upload=(datetime.datetime.now()+datetime.timedelta(days=-30)).strftime('%Y-%m-%d')
        # 格式化pre_days_upload, 为了比较大小
        Xdaysago = datetime.datetime.strptime(pre_days_upload,"%Y-%m-%d") 
        #下面两行为了查找重复实验号使用
        pre_days_upload1=(datetime.datetime.now()+datetime.timedelta(days=-7)).strftime('%Y-%m-%d')  
        mdaysago = datetime.datetime.strptime(pre_days_upload1,"%Y-%m-%d") 
           

        question=tk.messagebox.askokcancel(title='Warning',message='仅同步7日内的更新，请确保表格已关闭！ ***. 可能需要数分钟，请稍候！')
        if question:            
        
            # target_dir= r'C:\Users\lijun\Desktop\EDu03&EDu06 OTA测试信息'
            target_dir = r'\\10.114.184.9\Tcc_Rel\07 Project management\EDu03&EDu06 OTA测试信息'

            # -----------先看band对应表是否需要更新，如果日期比较新则更新。--------------
            bandfilepath = target_dir + '\Band对应表.xlsx'        
            bandfiletime = os.path.getmtime(bandfilepath)       
            timearr = time.localtime(bandfiletime)     #输出：time.struct_time(tm_year=2020, tm_mon=11, tm_mday=15, tm_hour=12, tm_min=49, tm_sec=10, tm_wday=6, tm_yday=320, tm_isdst=0)
            mytime = time.strftime('%Y-%m-%d %H:%M:%S',timearr)
            # print(mytime)    #2020-11-15 12:49:10
            # print(mdaysago)
            if mytime>str(mdaysago):                       
                #.打开Excel,读取worksheet           
                workbook = vb.load_workbook(bandfilepath)              
                cursor.execute('''delete from band_channel''') 

                query = '''insert into band_channel(Band,Tx_channel,Rx_channel,Date)values(%s,%s,%s,%s)'''
        
                worksheet = workbook['band对应']
                maxrows = worksheet.max_row 
                sheetzone=worksheet.iter_rows(min_row=2,max_row=maxrows,min_col=1,max_col=3)
                
                for row_excel in sheetzone:           
                    Band = row_excel[0].value
                    Tx_channel = row_excel[1].value 
                    Rx_channel = row_excel[2].value                 
                    Date=todaycolumn     

                    if Band==None:                    
                        Band=''
                    if Tx_channel==None:
                        Tx_cChannel=''    
                    if Rx_channel==None:
                        Rx_channel=''            

                    values =(Band,Tx_channel,Rx_channel,Date)
                    # 执行sql语句
                    cursor.execute(query,values) 
                    conn.commit() 
                tk.messagebox.showinfo(title='Hi', message='Band/channel信息已更新！   确定后继续更新其它信息.........')

                #如果band对应表有更新，则进行记录。
                cursor.execute('''insert into GTS_update_record(update_infor,name,update_date)values('Band/channel 信息已更新！',%s,%s)''',(user_name,update_time))
                conn.commit() 
           
            # 看ALT Test Information 表格是否需要更新，如果日期比较新则更新。
            altfilepath = target_dir + '\ALT Test Information.xlsx'        
            altfiletime = os.path.getmtime(altfilepath)       
            timearr = time.localtime(altfiletime)     #输出：time.struct_time(tm_year=2020, tm_mon=11, tm_mday=15, tm_hour=12, tm_min=49, tm_sec=10, tm_wday=6, tm_yday=320, tm_isdst=0)
            mytime = time.strftime('%Y-%m-%d %H:%M:%S',timearr)
            # print(mytime)    #2020-11-15 12:49:10
            if mytime>str(mdaysago):            
                #.打开Excel,读取worksheet           
                workbook = vb.load_workbook(altfilepath)              
                cursor.execute('''delete from alt_infor''') 

                query = '''insert into alt_infor(Product,Category,Platform,ALT_Score,ALT_Script,Signaling,LTE,Manual_Function,Auto_Function,WLAN_BT_GPS,Desence,
                IP_level,Charger_info,Sub6_5G,mmw_5G,Barometer,Date,Special)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
        
                worksheet = workbook.active
                maxrows = worksheet.max_row 

                sheetzone=worksheet.iter_rows(min_row=2,max_row=maxrows,min_col=1,max_col=18)
                
                for row_excel in sheetzone:           
                    Product = row_excel[0].value
                    Category = row_excel[1].value
                    Platform = row_excel[2].value
                    ALT_Score = row_excel[3].value
                    ALT_Script = row_excel[4].value  
                    Signaling = row_excel[5].value  
                    LTE = row_excel[6].value  
                    Manual_Function = row_excel[7].value  
                    Auto_Function = row_excel[8].value  
                    WLAN_BT_GPS = row_excel[9].value  
                    Desence = row_excel[10].value  
                    IP_level = row_excel[11].value  
                    Charger_info = row_excel[12].value  
                    Sub6_5G = row_excel[13].value  
                    mmw_5G = row_excel[14].value  
                    Barometer = row_excel[15].value                
                    Date=todaycolumn  
                    Special= row_excel[16].value  

                    #下面写法不可以
                    # list=[Product,ALT_Script,Signaling,LTE,Manual_Function,Auto_Function,WLAN_BT_GPS,Desence,Gost_touch,Camera_parameter,Sub6_5G,mmw_5G,Barometer]
                    # for item in zip(list):
                    #     if item==None:                                                
                    #         item=''
                    # if Camera_parameter==None:                    
                    #     Camera_parameter=''   
                    if Product==None:
                        Product=''
                    if Category==None:
                        Category=''
                    if Platform==None:
                        Platform=''
                    if ALT_Score==None:
                        ALT_Score='1'
                    if ALT_Script==None:
                        ALT_Script=''
                    if Signaling==None:
                        Signaling=''
                    if LTE==None:
                        LTE=''
                    if Manual_Function==None:
                        Manual_Function=''
                    if Auto_Function==None:
                        Auto_Function=''
                    if WLAN_BT_GPS==None:
                        WLAN_BT_GPS=''
                    if Desence==None:
                        Desence=''
                    if IP_level==None:
                        IP_level=''
                    if Charger_info==None:
                        Charger_info=''
                    if Sub6_5G==None:
                        Sub6_5G=''
                    if mmw_5G==None:
                        mmw_5G=''
                    if Barometer==None: 
                        Barometer=''  
                    if Special==None: 
                        Special=''        

                    values =(Product,Category,Platform,ALT_Score,ALT_Script,Signaling,LTE,Manual_Function,Auto_Function,WLAN_BT_GPS,Desence,IP_level,Charger_info,Sub6_5G,mmw_5G,Barometer,Date,Special)
                    # 执行sql语句
                    cursor.execute(query,values) 
                    conn.commit() 
                tk.messagebox.showinfo(title='Hi', message='ALT Test Information已更新！  确定后继续更新其它信息.........')
                #如果ALT inform 有更新，则进行记录。
                cursor.execute('''insert into GTS_update_record(update_infor,name, update_date)values('ALT Test Information 已更新！',%s,%s)''',(user_name,update_time))
                conn.commit() 




            #-----------对除band/ALT/Edu06模板之外的文件进行操作：看日期来决定是否要更新。----------------
            #遍历列出目录下的所有文件名
            sign=0  #如果有产品信息更新，sign会被置1，否则保持为0
            filenameupdated='' 
            for file in os.listdir(target_dir):    
                filewithpath = target_dir+'\\'+file                   
                print('目录文件',file)   
                
                filetime = os.path.getmtime(filewithpath)       
                timearr = time.localtime(filetime)     #输出：time.struct_time(tm_year=2020, tm_mon=11, tm_mday=15, tm_hour=12, tm_min=49, tm_sec=10, tm_wday=6, tm_yday=320, tm_isdst=0)
                mytime = time.strftime('%Y-%m-%d %H:%M:%S',timearr)    

                

                if mytime>str(mdaysago) and file !='Band对应表.xlsx' and file !='ALT Test Information.xlsx' and file !='~$ALT Test Information.xlsx' and file != 'Desense' and file !='ESD Illustration' and file !='Test Report Template':   
                    sign=1  
                    # print(mytime)    #2020-11-15 12:49:10    
                    filename = os.path.splitext(file)[0]  
                    if filename=='Thumbs.db':
                        os.remove(filename)
                        print(filename,"deleted")

                    print('已更新文件',filename)
                    if filenameupdated=='':
                        filenameupdated='GTS测试信息更新:'+filename
                    else:
                        filenameupdated=filenameupdated+'/'+filename
                    #.打开Excel,读取worksheet           
                    workbook = vb.load_workbook(filewithpath)              
                    cursor.execute('''delete from ota_infor where product=%s''',filename) 

                    query = '''insert into ota_infor(Product,TRP_EIS,Antenna,Band,Channel,Angle,Station,Test_name,WLAN24G,WLAN5G,Desence,NFC,Date)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
            
                    worksheet = workbook[filename]
                    maxrows = worksheet.max_row                 
                    sheetzone=worksheet.iter_rows(min_row=2,max_row=maxrows,min_col=1,max_col=10)
                    
                    for row_excel in sheetzone:           
                        Product = filename
                        TRP_EIS = row_excel[0].value    
                        Antenna = row_excel[1].value
                        Band = row_excel[2].value 
                        Channel = ''
                        Angle = row_excel[3].value
                        Station = row_excel[4].value
                        Test_name = row_excel[5].value            
                        
                        WLAN24G = row_excel[6].value
                        WLAN5G = row_excel[7].value 
                        Desence = row_excel[8].value
                        NFC = row_excel[9].value   
                        Date=todaycolumn    

                        if TRP_EIS==None:                    
                            TRP_EIS=''
                        if Antenna==None:
                            Antenna='' 
                        if Band==None:                    
                            Band=''
                        if Station==None:                                                
                            Station=''
                        if Angle==None:
                            Angle=''
                        if WLAN24G==None:
                            WLAN24G=''
                        if WLAN5G==None:
                            WLAN5G=''  
                        if Desence==None:
                            Desence=''
                        if NFC==None:
                            NFC=''
                        
                        if Test_name!='EDu03' and Test_name!='EDu06':
                            print('Wrong Test Name!!!',Test_name,Antenna,Band,Angle,maxrows)
                            tk.messagebox.showinfo(title='Hi', message=Product+':'+'实验名称出错！名称只能是EDu03/EDu06。')  
                            # quit()     
                            sys.exit(0) 

                        if Test_name == 'EDu06':                            
                            values =(Product,TRP_EIS,Antenna,Band,Channel,Angle,Station,Test_name,WLAN24G,WLAN5G,Desence,NFC,Date)
                            # 执行sql语句
                            cursor.execute(query,values) 
                            conn.commit() 
                        if Test_name == 'EDu03':                       
                            for name in ['EDu03-2-Normal','EDu03-4-Normal','EDu03-6-Normal','EDu03-2-FT','EDu03-4-FT','EDu03-6-FT']:
                                
                                values =(Product,TRP_EIS,Antenna,Band,Channel,Angle,Station,name,WLAN24G,WLAN5G,Desence,NFC,Date)
                                # 执行sql语句
                                cursor.execute(query,values) 
                                conn.commit()                          


                        cursor.execute('''UPDATE ota_infor AS o SET CHANNEL=(SELECT Tx_CHANNEL FROM band_channel WHERE band=o.Band) where TRP_EIS='TRP' ''')
                        conn.commit() 
                        cursor.execute('''UPDATE ota_infor AS o SET CHANNEL=(SELECT Rx_CHANNEL FROM band_channel WHERE band=o.Band) where TRP_EIS='EIS' ''')
                        conn.commit() 
            if sign==1:
                cursor.execute('''insert into GTS_update_record(update_infor,name,update_date)values(%s,%s,%s)''',(filenameupdated,user_name,update_time))
                conn.commit() 
                tk.messagebox.showinfo(title='Hi', message= filenameupdated)

            else:
                tk.messagebox.showinfo(title='Hi', message='产品GTS测试信息不需要更新！')

            email_summary()

                   


    def notice():
        tk.messagebox.showinfo(title='Hi', message='产品测试信息更新，请联系倪昭/彩虹或相应产品负责人！！！')

    def suggestion():
          
        def save_entry():
            infor=user_entry.get('1.0','end')
            print(infor)          
            sugg_name=user_entry1.get('1.0','end')  
            query='''insert into test_query_record (user,report,suggestion,query_time) values(%s,%s,%s,%s)'''
            user = getpass.getuser()  
            report=sugg_name+'/'+var.get()
            suggestion=infor
            query_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') 

            values =(user,report,suggestion,query_time)
            # 执行sql语句
            cursor.execute(query,values) 
            conn.commit() 
            tk.messagebox.showinfo(title='Hi', message='建议提交成功，谢谢！')
            window_suggestion.destroy()
            
        def suggestions_export():
            today=datetime.datetime.now().strftime('%m%d')
            tomorrow=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%m%d')
            todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')   
            tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d') 
            #建立mysql的连接
            conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
                    password='123456',port=3306,charset='utf8')    
            cursor = conn.cursor()

            pathfile = r'C:\自动分活儿\Suggestions Summary'+today+'.xlsx'
            workbook = vb.Workbook(pathfile)
            workbook.save(pathfile)
            workbook=vb.load_workbook(pathfile)
            worksheet1 = workbook.active


            fontstyle = vb.styles.Font(name='Times New Roman',bold=True,size=10)  #标题
            fontstyle1 = vb.styles.Font(name='Arial',bold=True,size=10)    #标题
            fontstyle2 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文红色
            fontstyle3 = vb.styles.Font(name='Arial',size=10,color='DC143C')  #正文红色
            alignstyle = vb.styles.Alignment(horizontal='left',vertical='center')  #居中
            alignstyle1 = vb.styles.Alignment(horizontal='left', vertical='center', text_rotation=0, wrap_text=True)

            cellbackground= vb.styles.PatternFill(fill_type='lightGray') 

            worksheet1['A1'] = 'Reporter'  
            worksheet1['B1'] = 'Time'  
            worksheet1['C1'] = 'Suggestions'



            cursor.execute('''SELECT COUNT(*) from test_query_record WHERE suggestion IS NOT NULL and query_time>='2023-04-01 17:10:42' ''')
            b=cursor.fetchone()  
            cursor.execute('''select report,query_time,suggestion from test_query_record WHERE suggestion IS NOT NULL and query_time>='2023-04-01 17:10:42' ''')
            allrows=cursor.fetchall()  
            for row in range(2,b[0]+2):      
                worksheet1.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle1
                worksheet1.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle1
                worksheet1.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle1 

            for cell in ['A1','B1','C1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle1
                worksheet1[cell].alignment = alignstyle

            # worksheet1['F1'].alignment = alignstyle 

            worksheet1.column_dimensions['A'].width = 10
            worksheet1.column_dimensions['B'].width = 20
            worksheet1.column_dimensions['C'].width = 100
        

            # worksheet1.row_dimensions[1].height =20

            
            workbook.save(pathfile)  

            print('输出表格数据成功！')
            tkinter.messagebox.showinfo(title='Hi', message='输出表格数据成功！ C:\自动分活儿')
            window_suggestion.destroy()


        var=tk.StringVar()
        var.set('月度例会') #初始选择状态
        # def r_selection():            
        #     r_selection_content=var.get()

        window_suggestion=tk.Toplevel(win)
        window_suggestion.geometry('550x280')
        window_suggestion.title('Suggestion')

        tk.Label(window_suggestion,text='欢迎大家提出关于实验室运营方面的建议，被采纳的有价值建议每条+1分。谢谢！').place(x=60,y=4)
        tk.Label(window_suggestion,text='建议内容：',fg='blue',font=('Times',9)).place(x=25,y=40)   
        tk.Label(window_suggestion,text='建议人姓名：',fg='blue',font=('Times',9)).place(x=25,y=130)   
        tk.Label(window_suggestion,text='沟通形式：',fg='blue',font=('Times',9)).place(x=280,y=130)   

        # user_sug=tk.StringVar()
        # user_entry=tk.Entry(window_suggestion,textvariable=user_sug,bd=4,justify=LEFT)
        # user_entry.place(x=40,y=70,width=450,height=90)
        user_entry=tk.Text(window_suggestion,width=70,height=4)
        user_entry.place(x=30,y=65)
        user_entry1=tk.Text(window_suggestion,width=15,height=1)
        user_entry1.place(x=30,y=160)
  
        r1=tk.Radiobutton(window_suggestion,text='One-One',variable=var,value='One-One')
        #点选时，A会被赋值给var,并执行command
        r1.place(x=280,y=150)   
        r2=tk.Radiobutton(window_suggestion,text='全员沟通会',variable=var,value='全员沟通会')
        r2.place(x=280,y=170)   


        btn_confirm_sign_up=tk.Button(window_suggestion,text='提交',width=12,height=1,command=save_entry,bg='#A0A0A0', fg='#0000FF')
        btn_confirm_sign_up.place(x=220,y=220)
        btn_confirm_sign_up=tk.Button(window_suggestion,text='导出',width=12,height=1,command=suggestions_export,bg='#A0A0A0', fg='#DC143C')
        #指定可以进行报告输出及邮件发送的人员名单
        if user_name == 'wangjin24' or user_name == 'lijun24' or user_name == 'nizhao2' or user_name == 'chenkai12':           
            btn_confirm_sign_up.place(x=330,y=220)
        
    def ver_infor():  
      
        help1='V2.0:ALT 信息中增加Special列，用于显示产品ALT中的特殊测试内容。' 
        help2='V2.0:OTA测试查询时显示该产品应该使用的测试站，GTS1200或GTS1800。'
        help3='V2.0:生成EDU03报告模板时可以区分样机数量，区分FT或Normal产品。'
        help4='V2.0:EDU03报告模板添加该产品静电图，图不存在无法创建模板。'
        help5='V2.0:EDU06报告模板添加Desense测试信息，不存在不影响模板创建。 '
        help6='V2.0:产品类型(FT/ODM/In-house)与模板不匹配时无法创建Edu03模板。 '
        help7='V2.0:ALT信息中新增产品类型及平台信息。 '       
        help8='V2.1:正确输入实验号，EDu06/Edu03模板可自动写入实验号及S/N。 ' 
        help9='V2.2:增加充电信息查询：“Charger Info”。去掉“Camera Parameter”。 ' 
        help10='V2.4:如实验号填写正确，可生成标准名称的模板，首页填好详细信息。 ' 
        help11='V2.5:更新“问题/建议收集”界面。'
        help12='V2.5:每次打开工具时，会检查浏览器版本，如有需要，自动下载更新谷歌驱动。'        
        help13='V2.6:解决软件偶尔没有反应的问题；更新“问题/建议收集”，增加汇总输出功能。'
        help14='V2.7:调整数据输出格式。'
        help15='V2.8:产品负责人可以自己更新产品测试信息，更新后邮件发送给相关人员。'
        help16='V3.1:不需要谷歌驱动更新，直接从ALTDB中查询实验号信息。'
        help17='V3.2:更换图标避免被当成病毒。'
        help18='V3.4:ALT信息查询中的Ghost touch选项取消，换成了防水等级查询IPx Level。'
        help=help1+'\n'+help2+'\n'+help3+'\n'+help4+'\n'+help5+'\n'+help6+'\n'+help7+'\n'+help8+'\n'+help9+'\n'+help10+'\n'+help11+'\n'+help12+'\n'+help13+'\n'+help14+'\n'+help15+'\n'+help16+'\n'+help17+'\n'+help18
        tk.messagebox.showinfo(title='版本更新记录', message=help)

    def sw_exit():   
        cursor.close()
        conn.close()         
        # driver_download.chromedriver_download()
        # taskskill_2left()
        sys.exit()   


    btn_query=Button(win,text='查询',width=12,height=1,command=query,bg='#C0C0C0', fg='#DC143C')
    btn_query.place(x=40,y=140)
    btn_exit=Button(win,text='退出',width=12,height=1,command=sw_exit,bg='#A0A0A0', fg='#0000FF' )
    btn_exit.place(x=270,y=140)
    btn_exit=Button(win,text='问题/建议反馈',width=12,height=1,command=suggestion,bg='#A0A0A0', fg='#DC143C' )
    btn_exit.place(x=520,y=140)
    # btn_exit=Button(win,text='近日ESD',width=12,height=1,command=ver_infor,bg='#A0A0A0', fg='#0000FF' )
    btn_exit.place(x=630,y=140)
    btn_exit=Button(win,text='版本记录',width=12,height=1,command=ver_infor,bg='#A0A0A0', fg='#0000FF' )
    btn_exit.place(x=740,y=140)

    #获取用户名
    user_name = getpass.getuser()   
    print(user_name)

    # if user_name == 'wangjin24' or user_name == 'nizhao2' :
    if user_name in ['lijun24','nizhao2','pengch2','wangjin24','chenkai12','wanghuan32','zhaojk1','zhaojk2','zhangyq14','xiongcq2','xuhang3','tanpl1','zhangyu90']:
        btn_update=Button(win,text='更新',width=12,height=1,command=update,bg='#A0A0A0', fg='#0000FF')
        btn_update.place(x=155,y=140)

    else:
        btn_update=Button(win,text='更新',width=12,height=1,command=notice,bg='#A0A0A0', fg='#0000FF')
        btn_update.place(x=155,y=140)

    


    

    tabel_frame = Frame(win)
    tabel_frame.pack(side='bottom',padx=10,pady=10)
    xscroll = Scrollbar(tabel_frame, orient=HORIZONTAL)
    yscroll = Scrollbar(tabel_frame, orient=VERTICAL)

    columns = ['Product','TRP_EIS','Antenna','Band','Channel','Angle','Station','Test_name','WLAN_2.4G','WLAN_5G','Desence','NFC']
    table = ttk.Treeview(
            master=tabel_frame,  # 父容器
            height=8,  # 表格显示的行数,height行
            columns=columns,  # 显示的列
            show='headings',  # 隐藏首列
            xscrollcommand=xscroll.set,  # x轴滚动条
            yscrollcommand=yscroll.set,  # y轴滚动
            )
    for column in columns:
        table.heading(column=column, text=column, anchor=CENTER)  # 定义表头
        table.column(column=column, width=60, minwidth=60, anchor=CENTER, )  # 定义列
    table.column(column=columns[0],width=110, anchor=CENTER)
    table.column(column=columns[4],width=105, anchor=CENTER)
    table.column(column=columns[5],width=80, anchor=CENTER)
    table.column(column=columns[6],width=80, anchor=CENTER)
    table.column(column=columns[7],width=80, anchor=CENTER)
    table.column(column=columns[8],width=80, anchor=CENTER)
    table.column(column=columns[9],width=80, anchor=CENTER)
    table.column(column=columns[10],width=60, anchor=CENTER)
    table.column(column=columns[11],width=60, anchor=CENTER)

    xscroll.config(command=table.xview)
    xscroll.pack(side=BOTTOM, fill=X)
    yscroll.config(command=table.yview)
    yscroll.pack(side=RIGHT, fill=Y)
    table.pack(fill=BOTH, expand=True)
    table.tag_configure('tag',background='#FFFACD',foreground='blue',)

 



    tabel_frame0 = Frame(win)
    # tabel_frame0.place(x=10,y=200)
    tabel_frame0.pack(side='bottom',padx=10,pady=10)
    xscroll = Scrollbar(tabel_frame0, orient=HORIZONTAL)
    yscroll = Scrollbar(tabel_frame0, orient=VERTICAL)

    columns0 = ['Product','Category','Platform','ALT_Score','ALT_Script','Signaling','LTE','Manual_Function','Auto_Function','WLAN_BT_GPS','Desence','IPx Level','Charger_Info','Sub6_5G','mmW_5G','Barometer','Special']
    table0 = ttk.Treeview(
            master=tabel_frame0,  # 父容器
            height=2,  # 表格显示的行数,height行
            columns=columns0,  # 显示的列
            show='headings',  # 隐藏首列
            xscrollcommand=xscroll.set,  # x轴滚动条
            yscrollcommand=yscroll.set,  # y轴滚动条
            
            )
    for column in columns0:
        table0.heading(column=column, text=column, anchor=CENTER)  # 定义表头
    #     table0.column(column=column, width=100, anchor=CENTER, )  # 定义列
    table0.column(column=columns0[0],width=110, anchor=CENTER)
    table0.column(column=columns0[1],width=70, anchor=CENTER)
    table0.column(column=columns0[2],width=70, anchor=CENTER)
    table0.column(column=columns0[3],width=70, anchor=CENTER)
    table0.column(column=columns0[4],width=70, anchor=CENTER)
    table0.column(column=columns0[5],width=70, anchor=CENTER)
    table0.column(column=columns0[6],width=70, anchor=CENTER)
    table0.column(column=columns0[7],width=105, anchor=CENTER)
    table0.column(column=columns0[8],width=100, anchor=CENTER)
    table0.column(column=columns0[9],width=100, anchor=CENTER)
    table0.column(column=columns0[10],width=80, anchor=CENTER)
    table0.column(column=columns0[11],width=80, anchor=CENTER)
    table0.column(column=columns0[12],width=120, anchor=CENTER)
    table0.column(column=columns0[13],width=80, anchor=CENTER)
    table0.column(column=columns0[14],width=80, anchor=CENTER)
    table0.column(column=columns0[15],width=80, anchor=CENTER)
    table0.column(column=columns0[16],width=250, anchor=CENTER)

    xscroll.config(command=table0.xview)
    xscroll.pack(side=BOTTOM, fill=X)
    yscroll.config(command=table0.yview)
    yscroll.pack(side=RIGHT, fill=Y)
    table0.pack(fill=BOTH, expand=True)

    table0.tag_configure('tag',background='#FFFACD',foreground='blue',)


    #软件版本判定： 版本号等于系统，什么也不做；小于系统，提示更新；大于系统，更新系统。
    cursor.execute('''select max(version) from version_infor where sw_name='GTS' ''')
    ver_infor=cursor.fetchone()
    ver_number=ver_infor[0]
    print(ver_number,version)
    if ver_number < version:
        cursor.execute('''INSERT INTO version_infor (sw_name,VERSION,DATE,comments)VALUES('GTS',%s,%s,%s) ''',(version,todaycolumn,comments))
        conn.commit()

    if ver_number > version:
        address=r'\\10.114.184.9\tcc_rel\03 Software\12 自动化小工具'
        tk.messagebox.showwarning(title='Hi', message='软件已升级到 V'+str(ver_number)+'！ 请到服务器下载！' + address)
        # taskskill_2left()
        sys.exit()

    #关闭一下chromedriver的进程，这一句本来是放在chromedriver_download中的，如果下载放在打开程序时，则不需要本句。
    # os.system('taskkill /im chromedriver.exe /F')
    # driver_download.chromedriver_download()
    win.mainloop()





     

if __name__=="__main__":         
    # ip_address='localhost'
    ip_address='10.114.183.17'
    # ip_address='10.114.183.55'
    version=3.44 #先用44，避免44版失效
    release_date='2025-03-16'
    comments='服务器更新' 
    
    windows()

#变更记录：

#2.0更新：在alt_infor表中新增一列Special, 放在Date前；在OTA_infor中的Testname前增加一列Station, Testnanme长度变为50
#2.0更新：选择的实验为Edu06时，使用标准模板，并把Desense图粘贴进来
#2.0更新：把EDU03变成6种类型，对应6个不同模板，选择的实验为Edu03开始时，分别按六个模板处理，然后再把静电图添加进来。
#3.1更新：调用GTS_infor_crawler改成GTS_infor_crawler1，直接通过ALTDB数据库查询实验信息并返回。因此可以去掉下载谷歌驱动以及进度条。
#3.43是最新版本，本版本3.34是在3.33基础上去掉SC部分，其它不变。数据库中版本号还是3.33